package org.appFactory.shared;

import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.google.appengine.api.urlfetch.HTTPResponse;
import com.google.appengine.api.urlfetch.URLFetchService;
import com.google.appengine.api.urlfetch.URLFetchServiceFactory;
import com.opensymphony.xwork2.ActionContext;
import com.google.appengine.api.rdbms.AppEngineDriver;

public class Util {

	private static String instance_name = "carletonatom:testdb";
	private static String database_name = "events_development";
	private static java.sql.Connection conn = null;

	public static java.sql.Connection getDBConn() {
		try {
			if (conn == null || conn.isClosed())
				connect();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	private static void connect() {
		try {
			try {
				DriverManager.registerDriver(new AppEngineDriver());
			} catch (SQLException e) {
				error(e, "Unable to register driver");
			}
			conn = DriverManager.getConnection("jdbc:google:rdbms://"
					+ instance_name + "/" + database_name);
		} catch (SQLException ex) {
			error(ex, "Unable to connect to database");
			System.exit(1);
		}
	}

	public static void insertJSONEvents(JSONObject eventsData) {
		JSONArray events;
		try {
			events = eventsData.getJSONArray("data");

			for(int i=0;i<events.length();i++){
				JSONObject event = events.getJSONObject(i);
				JSONObject location = event.getJSONObject("venue").getJSONObject("location");
				String statement = "INSERT INTO events (id, place_id,name, latitude, longitude) VALUES( ? , ?, ? , ? , ?)";
				PreparedStatement stmt = getDBConn().prepareStatement(statement);
				stmt.setString(1, event.getString("id"));
				stmt.setString(2, event.getString("id"));
				stmt.setString(3, event.getString("name"));
				stmt.setDouble(4, location.getDouble("latitude"));
				stmt.setDouble(5, location.getDouble("longitude"));
				int success = 2;
				// To catch Primary Key Exception
				try {
					success = stmt.executeUpdate();
				}
				catch (SQLException e) {
					// TODO Auto-generated catch block
					//e.printStackTrace();
					System.out.println("Primary Key Violation for Event ID " + event.getString("id"));
				}
				if (success == 1) {
					System.out.println("Inserted Entry " + i + " of " + events.length());
				} else if (success == 0) {
					System.out.println("Failed Entry " + i);
				}
			}
		}catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static JSONObject fetchJSONEvents(String latitude, String longitude, String distance) {
		
		JSONObject eventsData = new JSONObject();
		JSONArray events = new JSONArray();

		String statement = "SELECT *, ((ACOS(SIN( ? * PI() / 180) * SIN(latitude * PI() / 180) + COS( ? * PI() / 180) * COS(latitude * PI() / 180) * COS(( ? - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `events` HAVING `distance`<= ? ORDER BY `distance` ASC ";	

		try {
			PreparedStatement stmt = getDBConn().prepareStatement(statement);
			stmt.setString(1, latitude);
			stmt.setString(2, latitude);
			stmt.setString(3, longitude);
			stmt.setString(4, distance);

			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			JSONObject event;
			JSONObject venue;
			JSONObject location;


			if(rs.next()) {
				do{
					event = new JSONObject();
					venue = new JSONObject();
					location = new JSONObject();

					location.put("longitude", rs.getDouble("longitude"));
					location.put("latitude", rs.getDouble("latitude"));
					location.put("zip", "NA");
					location.put("street", "NA");
					location.put("state", "NA");
					location.put("country", "NA");
					location.put("city", "NA");

					venue.put("location", location);

					event.put("id", rs.getString("id"));
					event.put("name", rs.getString("name"));
					event.put("start_time", "NA");
					event.put("end_time", "NA");
					event.put("location", "NA");
					event.put("timezone", "NA");
					event.put("venue", venue);

					events.put(event);
				} while (rs.next());
			}
			else {
				System.out.println("No Data");
			}
			eventsData.put("data", events);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return eventsData;
	}

	public static void putSessionObj(Object rec, String key) {
		if (ActionContext.getContext().getSession() != null) {
			ActionContext.getContext().getSession().put(key, rec);
		} else {
			throw new RuntimeException("Unable to associate Key: " + key
					+ " with the provided object. [No Session Found]");
		}
	}


	public static Object getSessionObj(String key) {

		if (ActionContext.getContext().getSession() != null) {
			Object record = ActionContext.getContext().getSession().get(key);
			return record;
		} else {
			throw new RuntimeException("Unable to retrieve object for Key: "
					+ key + " [No Session Found]");
		}

	}

	public static JSONObject getJSONResponse(String URL) {

		String plainResponse = getResponse(URL);
		try {
			return new JSONObject(plainResponse);
		} catch (JSONException e) {
			return new JSONObject();
		}

	}

	public static String getResponse(String s_url) {

		URLFetchService fetcher = URLFetchServiceFactory.getURLFetchService();

		try {

			URL url = new URL(s_url);
			HTTPResponse response = fetcher.fetch(url);
			String result;
			if (response.getResponseCode() == 200) {
				result = new String(response.getContent(), "UTF-8");
				return result;
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return "error";
	}

	private static void error(SQLException ex, String error) {
		if (error != null)
			System.err.println("Error: " + error + "");
		System.err.println("SQLException: " + ex.getMessage());
	}
}
